/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package UserManager;

import DBManager.DBConnection;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.security.MessageDigest;
import java.sql.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Locale;

/**
 *
 * @author TigerBoy
 */
public class UserManager {
    
    private int checker;
    
    private void userManagerLogs(String functionName,String status,String input)
    {
    
        String path = "C:/Foobar_Logs/userLogs.txt";
        
        try
        {
            File file = new File(path);
            FileWriter fw = new FileWriter( file , true);
            BufferedWriter bw = new BufferedWriter(fw);
           
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            java.util.Date date = new java.util.Date();
            String currentDate = dateFormat.format(date);
            
            bw.write("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"); bw.newLine();
            bw.write("Function:" + functionName + " has " + status + ""); bw.newLine();
            bw.write(input); bw.newLine();
            bw.write("Excecution Date and Time: " + currentDate); bw.newLine();
            
            bw.close();
            
        }
        catch (Exception e)
        {
            
            System.out.println(e);
            System.out.println("Writing to the text file: \n" + path + " has failed");
            System.out.println("Please Check if Diskspace is full");
            
        }
    
    }
    
    // one way hash
    public String hasher(String password){
        
        try{
            MessageDigest md = MessageDigest.getInstance("SHA-256");
            md.update(password.getBytes());
 
            byte byteData[] = md.digest();
 
            //convert the byte to hex format method 1
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < byteData.length; i++) {
                sb.append(Integer.toString((byteData[i] & 0xff) + 0x100, 16).substring(1));
            }
            
            return sb.toString();
        }catch(Exception e){
            return null;
        }
        
    }
    
    // register
    public boolean addUser(User newUser,CreditCard newCard)
    {
        
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            Connection conn2 = db.getConnection();
            
            conn.setAutoCommit(false);
            //Construct SQL Query
            PreparedStatement pstmt1 =
                conn.prepareStatement("INSERT "
                                + "INTO user(userType,nameFirst,nameMiddleI,nameLast,telephone,username,password,email,isActive,lockUser,creditNumber,addressDeliveryNumber,addressDeliveryStreet,addressDeliverySubdivision,addressDeliveryCity,addressDeliveryPostal,addressDeliveryCountry,created)"
                                + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            
            PreparedStatement pstmt2 =
                conn.prepareStatement("INSERT "
                                + "INTO creditcard(creditName,creditNumber,creditYear,creditMonth,creditCompany,"
                                + "addressBillNumber,addressBillStreet,addressBillSubdivision,addressBillCity,addressBillPostal,addressBillCountry,dateCreated)"
                                + "values(?,?,?,?,?,?,?,?,?,?,?,?)");
            
            PreparedStatement pstmt5 = conn2.prepareStatement("Select * FROM user WHERE username=?");
            
            //append values for the parameters
            
            int i=1;
            pstmt5.setString(i++, newUser.getUsername());
            
            
            
            // for log purposes
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            java.util.Date date = new java.util.Date();
            String currentDate = dateFormat.format(date);
            
            /* for user*/
            i=1;
            pstmt1.setString(i++, newUser.getUserType());
            pstmt1.setString(i++, newUser.getNameFirst());
            pstmt1.setString(i++, newUser.getNameMiddleI());
            pstmt1.setString(i++, newUser.getNameLast());
            pstmt1.setString(i++, String.valueOf(newUser.getTelephone()));
            pstmt1.setString(i++, newUser.getUsername());
            pstmt1.setString(i++, hasher(newUser.getPassword()));
            pstmt1.setString(i++, newUser.getEmail());
            pstmt1.setString(i++, String.valueOf(newUser.isIsActive()));
            pstmt1.setString(i++, String.valueOf(0));
            pstmt1.setString(i++, newUser.getCreditNumber());
            pstmt1.setString(i++, newUser.getAddressDeliveryNumber());
            pstmt1.setString(i++, newUser.getAddressDeliveryStreet());
            pstmt1.setString(i++, newUser.getAddressDeliverySubdivision());
            pstmt1.setString(i++, newUser.getAddressDeliveryCity());
            pstmt1.setString(i++, newUser.getAddressDeliveryPostal());
            pstmt1.setString(i++, newUser.getAddressDeliveryCountry());
            pstmt1.setString(i++, currentDate);
           
                       
            /* for credit card*/
            i=1;
            pstmt2.setString(i++, newCard.getCreditName());
            pstmt2.setString(i++, newCard.getCreditNumber());
            pstmt2.setString(i++, String.valueOf(newCard.getCreditYear()));
            pstmt2.setString(i++, String.valueOf(newCard.getCreditMonth()));
            pstmt2.setString(i++, newCard.getCreditCompany());
            pstmt2.setString(i++, newCard.getAddressBillNumber());
            pstmt2.setString(i++, newCard.getAddressBillStreet());
            pstmt2.setString(i++, newCard.getAddressBillSubdivision());
            pstmt2.setString(i++, newCard.getAddressBillCity());
            pstmt2.setString(i++, newCard.getAddressBillPostal());
            pstmt2.setString(i++, newCard.getAddressBillCountry());
            pstmt2.setString(i++, currentDate);
            
            
 
            //execute SQL statement
            pstmt1.executeUpdate();
            pstmt2.executeUpdate();
            
            //Checker for same username
            ResultSet rst = pstmt5.executeQuery();
            checker=0;
            
            while(rst.next())
            {
                checker++;   
            }
            
            
            if(checker>0)
            {
                return false;
            }
            else
            {
                conn.commit();
                pstmt1.close();
                pstmt2.close();
                return true;
            }
            
            
        }catch(SQLException ex)
        {
            
            System.out.println("addUser() has failed to execute");
        
        }
        
            
        return false;
            
        
        
    }
    
    public boolean unlockAccount(String username)
    {
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE user SET isActive=? WHERE username=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, String.valueOf(5));
            pstmt.setString(i++, username);
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
            
            System.out.println("unlockAccount() has failed to execute");
        
        }
        
        
        return result;
        
    }
    
    public boolean lockAccount(String username)
    {
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE user SET isActive=? WHERE username=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, String.valueOf(0));
            pstmt.setString(i++, username);
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
        
            System.out.println("lockAccount() has failed to execute");
            
            
        }
        
        return result;
        
    }
    
    public boolean adminLockAccount(String username)
    {
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE user SET lockUser=? WHERE username=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, String.valueOf(1));
            pstmt.setString(i++, username);
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
        
            System.out.println("adminLockAccount() has failed to execute");
            
            
        }
        
        return result;
        
    }
    
    public boolean adminUnlockAccount(String username)
    {
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE user SET lockUser=? WHERE username=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, String.valueOf(0));
            pstmt.setString(i++, username);
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
            
            System.out.println("adminUnlockAccount() has failed to execute");
        
        }
        
        
        return result;
        
    }
    
    public int logIn(String username,String password)
    {
    
        //    logIn() returns
        //    1 = lockout
        //    2 = Admin Expire Pass
        //    3 = Wrong password
        //    4 = Success Login
        //    5 = No User Exists
        //    6 = Fail Function
        //    7 = ADMIN LOCK
        User newUser = new User();
        int isActive;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn1 = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt1 =
                conn1.prepareStatement("SELECT * FROM user WHERE username=?");
            // If username exists but password is wrong
            Connection conn2 = db.getConnection();
            PreparedStatement pstmt2 = conn2.prepareStatement("UPDATE user SET isActive=? WHERE username=?");
            
            Connection conn3 = db.getConnection();
            PreparedStatement pstmt3 = conn3.prepareStatement("UPDATE user SET lastLogIn=? WHERE username=?");
            
            
            //append values for the parameters
            int i=1;
            
            pstmt1.setString(i++, username);
            
            //execute SQL statement 
            ResultSet rst = pstmt1.executeQuery();
            
            
            checker=0;
            
            while(rst.next())
            {
                checker++;
                newUser.setUserType(rst.getString("userType"));
                newUser.setUsername(username);
                newUser.setPassword(rst.getString("password"));
                newUser.setCreditNumber(rst.getString("creditNumber"));
                newUser.setIsActive(rst.getInt("isActive"));
                newUser.setLockuser(rst.getInt("lockUser"));
                newUser.setDateCreated(rst.getString("created"));
                newUser.setLastLogIn(rst.getString("lastLogIn"));
                
            }
            isActive = newUser.isIsActive();
            
            if(checker!=1)
            {
                return 5;
            
            }
            
            if(newUser.getLockuser() == 1)
            {
            
                return 7;
            }
            
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            java.util.Date date = new java.util.Date();
            String currentDate = dateFormat.format(date);
            i=1;
            
            
            
            if(isActive==0 &&  !( newUser.getLastLogIn() == null))
            {
                
                // If User still loged in within 5 minutes and account locked = true
                if(!( lockOutReleaseCheck(newUser.getLastLogIn()) ) )
                {
                    pstmt3.setString(i++, currentDate);
                    pstmt3.setString(i++, newUser.getUsername());
                    pstmt3.executeUpdate();
                    pstmt3.close();
                    return 1;
                    
                }
                else // if 5 minutes has passed and account is locked
                if( lockOutReleaseCheck(newUser.getLastLogIn()))
                {
                
                    pstmt3.setString(i++, currentDate);
                    pstmt3.setString(i++, newUser.getUsername());
                    pstmt3.executeUpdate();
                    pstmt3.close();
                    unlockAccount(newUser.getUsername());
                    isActive = 5;
                }
                
                
            } 
            else // Account is active and we are just loging the last log in time
            {
                
                pstmt3.setString(i++, currentDate);
                pstmt3.setString(i++, newUser.getUsername());
                pstmt3.executeUpdate();
                pstmt3.close();
            
            }
            
            
            
//             If Administrators check if temporary password was expired
//             If cardNumber == 1, Already Changed, if 0 not yet
            if( !( (newUser.getUserType()).equals("Customer") ) && !( (newUser.getUserType()).equals("Administrator") ) && newUser.getCreditNumber().equals("0") && isActive > 0)
            {
                // Checks if temporary password was expired
                boolean expiredPassword;
                expiredPassword = isPasswordExpired(newUser.getDateCreated());
                
                if(expiredPassword == true)
                {
                    lockAccount(username);
                    return 2;
                
                }
                
            }
            
            
            if(checker>0 && !( (hasher(password)).equals(newUser.getPassword()) ) && isActive > 0 && !( (newUser.getUserType()).equals("Administrator") ))
            {
                i=1;
                pstmt2.setString(i++, String.valueOf(isActive-1));
                pstmt2.setString(i++, newUser.getUsername());
                pstmt2.executeUpdate();
                pstmt2.close();
                return 3;
                
            }
            else
            if(isActive > 0 && (hasher(password)).equals(newUser.getPassword()))
            {
                
                i=1;
                pstmt2.setString(i++, String.valueOf(5));
                pstmt2.setString(i++, newUser.getUsername());
                pstmt2.executeUpdate();
                pstmt2.close();
                return 4;
                
            }
            else
            {
                return 3;
            }
            
            
            
        }
        catch(SQLException ex)
        {
        
            System.out.println("logIn() has failed to execute");
            
        }
        
        return 6;
        
        
        
        
    }
    
    public boolean isPasswordExpired(String dateCreated)
    {
               
        try {
            
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US );
            java.util.Date created = df.parse( dateCreated ); //Format --> 2013-03-14 00:36:48
            java.util.Date now = new java.util.Date();
            long diffInMilliseconds = now.getTime() - created.getTime();

            double diffInHours = diffInMilliseconds / 3600000D;

            if(Math.abs(diffInHours)>24)
            {
                return true;
            }
        
        } catch ( ParseException exc) {
            
            System.out.println("System Time has an error");
            
        }
        
        return false;
        
    }

    public boolean lockOutReleaseCheck(String lastLogIn)
    {
               
        try {
            
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US );
            java.util.Date created = df.parse( lastLogIn ); //Format --> 2013-03-14 00:36:48
            java.util.Date now = new java.util.Date();
            long diffInMilliseconds = now.getTime() - created.getTime();

            double diffInMinutes = diffInMilliseconds / 60000D;
            // this is 1 minute, *60 for 1 hour,
            
            
            if(Math.abs(diffInMinutes)>5)
            {
                return true;
                
            }
            
        
        } catch ( ParseException exc) {
            
            System.out.println("System Time has an error");
            
        }
        
        return false;
        
    }
    
    public boolean changePassword(String username,String password)
    {
        
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE user SET password=? WHERE username=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, hasher(password));
            pstmt.setString(i++, username);
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
        
            System.out.println("changePassword() has failed to execute");
            
            
        }
        
        return result;
        
    
    
    }
    
    public boolean changeAdminPassword(User newUser)
    {
        
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE user SET password=?,creditNumber=? WHERE username=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, hasher(newUser.getPassword()));
            pstmt.setString(i++, "1");
            pstmt.setString(i++, newUser.getUsername());
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
        
            System.out.println("changeAdminPassword() has failed to execute");
            
            
        }
        
        return result;
        
    
    
    }
    
    public void viewUser(User UserData)
    {
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt = conn.prepareStatement("Select * FROM user WHERE username=?");
            
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, UserData.getUsername());
            
            //execute SQL statement
            ResultSet rst = pstmt.executeQuery();
            
            
            while(rst.next())
            {
                UserData.setUserType(rst.getString("userType"));
                UserData.setNameFirst(rst.getString("nameFirst"));
                UserData.setNameMiddleI(rst.getString("nameMiddleI"));
                UserData.setNameLast(rst.getString("nameLast"));
                UserData.setTelephone(rst.getInt("telephone"));
                UserData.setUsername(rst.getString("username"));
                UserData.setPassword(rst.getString("password"));
                UserData.setEmail(rst.getString("email"));
                UserData.setIsActive(rst.getInt("isActive"));
                UserData.setCreditNumber(rst.getString("creditNumber"));
                UserData.setAddressDeliveryNumber(rst.getString("addressDeliveryNumber"));
                UserData.setAddressDeliveryStreet(rst.getString("addressDeliveryStreet"));
                UserData.setAddressDeliverySubdivision(rst.getString("addressDeliverySubdivision"));
                UserData.setAddressDeliveryCity(rst.getString("addressDeliveryCity"));
                UserData.setAddressDeliveryPostal(rst.getString("addressDeliveryPostal"));
                UserData.setAddressDeliveryCountry(rst.getString("addressDeliveryCountry"));
                UserData.setDateCreated(rst.getString("created"));
            }
        }
        catch(SQLException ex)
        {
            System.out.println("viewUser() has failed to execute");
        }
    }
    
    public String checkUserType(String username)
    {
        String userType = null;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt = conn.prepareStatement("Select userType FROM user WHERE username=?");
            
            //append values for the parameters
            int i=1;
            
            pstmt.setString(i++, username);
            
            //execute SQL statement
            ResultSet rst = pstmt.executeQuery();
            
            
            while(rst.next())
            {
                
                userType = rst.getString("userType");
                
                
            }
            
            
        }
        catch(SQLException ex)
        {
        
            System.out.println("checkUserType() has failed to execute");
            
        }
        
        
    
        return userType;
    }
    
    public int checkIsActive(String username)
    {
        int isActive;
        isActive = 0;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt = conn.prepareStatement("Select isActive FROM user WHERE username=?");
            
            //append values for the parameters
            int i=1;
            
            pstmt.setString(i++, username);
            
            //execute SQL statement
            ResultSet rst = pstmt.executeQuery();
            
            
            while(rst.next())
            {
                
                isActive = rst.getInt("isActive");
                
                
            }
            
            
        }
        catch(SQLException ex)
        {
        
            System.out.println("checkUserType() has failed to execute");
            
        }
        
        
    
        return isActive;
    }

    public boolean addManager(User newUser)
    {
        
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            Connection conn2 = db.getConnection();
            
            conn.setAutoCommit(false);
            //Construct SQL Query
            PreparedStatement pstmt1 =
                conn.prepareStatement("INSERT "
                                + "INTO user(userType,nameFirst,nameMiddleI,nameLast,telephone,username,password,email,isActive,creditNumber,addressDeliveryNumber,addressDeliveryStreet,addressDeliverySubdivision,addressDeliveryCity,addressDeliveryPostal,addressDeliveryCountry,created)"
                                + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
            
            
            PreparedStatement pstmt5 = conn2.prepareStatement("Select * FROM user WHERE username=?");
            
            //append values for the parameters
            
            int i=1;
            pstmt5.setString(i++, newUser.getUsername());
            
            
            
            // for log purposes
            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            java.util.Date date = new java.util.Date();
            String currentDate = dateFormat.format(date);
            
            /* for user*/
            i=1;
            pstmt1.setString(i++, newUser.getUserType());
            pstmt1.setString(i++, newUser.getNameFirst());
            pstmt1.setString(i++, newUser.getNameMiddleI());
            pstmt1.setString(i++, newUser.getNameLast());
            pstmt1.setString(i++, String.valueOf(newUser.getTelephone()));
            pstmt1.setString(i++, newUser.getUsername());
            pstmt1.setString(i++, hasher(newUser.getPassword()));
            pstmt1.setString(i++, newUser.getEmail());
            pstmt1.setString(i++, String.valueOf(newUser.isIsActive()));
            pstmt1.setString(i++, "0");
            pstmt1.setString(i++, newUser.getAddressDeliveryNumber());
            pstmt1.setString(i++, newUser.getAddressDeliveryStreet());
            pstmt1.setString(i++, newUser.getAddressDeliverySubdivision());
            pstmt1.setString(i++, newUser.getAddressDeliveryCity());
            pstmt1.setString(i++, newUser.getAddressDeliveryPostal());
            pstmt1.setString(i++, newUser.getAddressDeliveryCountry());
            pstmt1.setString(i++, currentDate);
            
            
            
 
            //execute SQL statement
            pstmt1.executeUpdate();
            
            //Checker for same username
            ResultSet rst = pstmt5.executeQuery();
            checker=0;
            
            while(rst.next())
            {
                checker++;   
            }
            
            
            if(checker>0)
            {
                return false;
            }
            else
            {
                conn.commit();
                pstmt1.close();
                return true;
            }
            
            
        }catch(SQLException ex)
        {
            
            System.out.println("addManager() has failed to execute");
        
        }
        
                    
        return false;
            
        
        
    }
    
    public boolean changeInformation(User newUser)
    {
        boolean result = false;
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt =
                conn.prepareStatement("UPDATE user SET telephone=?,email=?,addressDeliveryNumber=?,addressDeliveryStreet=?,addressDeliverySubdivision=?,addressDeliveryCity=?,addressDeliveryPostal=?,addressDeliveryCountry=? WHERE username=?");
           
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, String.valueOf(newUser.getTelephone()));
            pstmt.setString(i++, newUser.getEmail());
            pstmt.setString(i++, newUser.getAddressDeliveryNumber());
            pstmt.setString(i++, newUser.getAddressDeliveryStreet());
            pstmt.setString(i++, newUser.getAddressDeliverySubdivision());
            pstmt.setString(i++, newUser.getAddressDeliveryCity());
            pstmt.setString(i++, newUser.getAddressDeliveryPostal());
            pstmt.setString(i++, newUser.getAddressDeliveryCountry());
            pstmt.setString(i++, newUser.getUsername());
            
            
            //execute SQL statement
            pstmt.executeUpdate();
            
            result = true;
        }
        catch(SQLException ex){
        
            System.out.println("changeInformation() has failed to execute");
            
            
        }
        
        return result;
        
    
    }
    
    public boolean verifyPassword(String username, String password)
    {
        boolean result = false;
    
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt = conn.prepareStatement("Select * FROM user WHERE username=?");
            
            //append values for the parameters
            int i=1;
            pstmt.setString(i++, username);
            
            //execute SQL statement
            ResultSet rst = pstmt.executeQuery();
            
            String CMPpassword = "";
            
            while(rst.next())
            {
                CMPpassword = rst.getString("password");
            }
            
            if(password.equals(CMPpassword))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch(SQLException ex)
        {
            System.out.println("verifyPassword() has failed to execute");
        }
        
        return result;
    }
    
    public float viewTotalSales()
    {
        
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt = conn.prepareStatement("Select * FROM transaction");
            
            //execute SQL statement
            ResultSet rst = pstmt.executeQuery();
            
            float total = 0;
            
            while(rst.next())
            {
                total = total + (( rst.getFloat("price") ) * ( rst.getInt("quantity") ));
            
            }
            
            pstmt.close();
            return total;
            
        }
        catch(SQLException ex)
        {
            System.out.println("viewTotalSales() has failed to execute");
        }
        
        return -1;
    
    }
    
    public float viewSalesPerProduct(int productID)
    {
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt = conn.prepareStatement("Select * FROM transaction where productID=?");
            
            int i = 1;
            pstmt.setString(i++, String.valueOf(productID));
            
            //execute SQL statement
            ResultSet rst = pstmt.executeQuery();
            
            float total = 0;
            
            while(rst.next())
            {
                total = total + (( rst.getFloat("price") ) * ( rst.getInt("quantity") ));
            
            }
            
            pstmt.close();
            return total;
            
        }
        catch(SQLException ex)
        {
            System.out.println("viewSalesPerProduct() has failed to execute");
        }
        
        return -1;
    
    
    }
    
    public float viewSalesPerProductType(String productType)
    {
        try{
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            
            //Construct SQL Query
            PreparedStatement pstmt = conn.prepareStatement("Select * FROM transaction where productType=?");
            
            int i = 1;
            pstmt.setString(i++, String.valueOf(productType));
            
            //execute SQL statement
            ResultSet rst = pstmt.executeQuery();
            
            float total = 0;
            
            while(rst.next())
            {
                total = total + (( rst.getFloat("price") ) * ( rst.getInt("quantity") ));
            
            }
            
            pstmt.close();
            return total;
            
        }
        catch(SQLException ex)
        {
            System.out.println("viewSalesPerProductType() has failed to execute");
        }
        
        return -1;
    
    
    
    }
    
}
